﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class LinqToSqlDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
            DdlCategory.DataValueField = "Mark";
            DdlCategory.DataTextField = "Name";
            DdlCategory.DataSource = new TestDBDataContext().MyCategory;
            DdlCategory.DataBind();
        }
    }

    //查询事件实现
    protected void BtnSearch_Click(object sender, EventArgs e)
    {
        string searchKey = TbKeyword.Text.Trim();
        GridView1.DataSource = GetData(searchKey);
        GridView1.DataBind();
    }

    //分页事件实现
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        //绑定数据
        Bind();
    }

    //编辑功能的实现
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        //绑定数据
        Bind();
    }
    //编辑事件之取消功能的实现
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        //GridView1.EditIndex默认为-1
        GridView1.EditIndex = -1;
        //绑定数据
        Bind();
    }
    //编辑事件之更新功能的实现
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        GridViewRow selectRow = GridView1.Rows[e.RowIndex];
        int id = Convert.ToInt32(selectRow.Cells[1].Text);
        string title = (selectRow.Cells[2].Controls[0] as TextBox).Text;
        string content = (selectRow.Cells[3].Controls[0] as TextBox).Text;
        string author = (selectRow.Cells[5].Controls[0] as TextBox).Text;
        int clickNumber = Convert.ToInt32((selectRow.Cells[6].Controls[0] as TextBox).Text);
        updateData(id, title, content, author, clickNumber);
        //更新完成后，把当前页作为默认页
        GridView1.EditIndex = -1;
        //绑定数据
        Bind();
    }

    //删除功能的实现
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        var deleteId = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
        deleteData(deleteId);
        Bind();
    }

    //新增事件的实现
    protected void BtnAdd_Click(object sender, EventArgs e)
    {
        Myinfo data = new Myinfo();
        data.Title = TbTitle.Text.Trim();
        data.Content = TbContent.Text.Trim();
        data.Author = TbAuthor.Text.Trim();
        data.Publish_Time = DateTime.Now;
        data.Click_Number = 0;
        data.Category_Mark = DdlCategory.SelectedValue;
        insertData(data);
        Bind();
    }

    private void Bind()
    {
        //设置Id不被修改，创建数据库时Id内部自增，不能修改
        GridView1.DataKeyNames = new string[] { "Id" };//定义GridView中行的key
        //数据源的绑定
        GridView1.DataSource = GetData();
        GridView1.DataBind();
    }

    private object GetData()
    {
        //初始化数据库，即连接数据库,打开“Test”数据库
        var db = new TestDBDataContext();
        //获取info数据表中的数据
        //var results = db.Myinfo;

        //From  子句：指定查询操作的数据源和范围变量
        var results = from info in db.Myinfo
                      select info;

        //Where  子句:指定筛选操作的逻辑条件
        //var results = from info in db.Myinfo
        //              where info.Click_Number >= 500
        //              select info;

        //OrderBy 子句：对查询结果进行排序
        //var results = from info in db.Myinfo
        //              orderby info.Publish_Time descending
        //              select info;

        //Select  子句：指定查询结果的类型和表现形式
        //Select子句指定字段显示,即只显示标题、内容、作者
        //var results = from info in db.Myinfo
        //              orderby info.Publish_Time descending
        //              select new
        //              {
        //                  info.Title,
        //                  info.Content,
        //                  info.Author
        //              };

        //Select子句 指定字段名
        //var results = from info in db.Myinfo
        //              orderby info.Publish_Time descending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  内容 = info.Content,
        //                  作者 = info.Author
        //              };

        //显示关联数据
        //var results = from info in db.Myinfo
        //              orderby info.Publish_Time descending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  内容 = info.Content,
        //                  分类 = info.MyCategory.Name
        //              };

        //显示没有定义关系的关联数据
        //var results = from info in db.Myinfo
        //              join category in db.MyCategory on info.Category_Mark equals category.Mark
        //              orderby info.Publish_Time descending
        //              select new
        //              {
        //                  标题 = info.Title,
        //                  内容 = info.Content,
        //                  分类 = category.Name
        //              };

        //Linq查询的方法
        //var results = db.Myinfo.Select(res=>res.Title);
        //var results = db.Myinfo.Sum(res => res.Click_Number);
        //var results = db.Myinfo.Count();
        //var results = db.Myinfo.Take(3);


        Debug.WriteLine(results);

        return results;
    }

    private object GetData(string searchKey)
    {
        var db = new TestDBDataContext();
        //以关键字开始
        //var results = from info in db.Myinfo
        //              where info.Title.StartsWith(searchKey)
        //              select info;

        //以关键字结束
        //var results = from info in db.Myinfo
        //              where info.Title.EndsWith(searchKey)
        //              select info;

        //包含关键字
        //var results = from info in db.Myinfo
        //              where info.Title.Contains(searchKey)
        //              select info;

        //使用like方法
        var results = from info in db.Myinfo
                      where System.Data.Linq.SqlClient.SqlMethods.Like(info.Title, "%" + searchKey + "%")
                      select info;


        Debug.WriteLine(results);
        return results;
    }

    private void updateData(int id, string title, string content, string author, int clickNumber)
    {
        //初始化数据库，即连接数据库,打开“Test”数据库
        var db = new TestDBDataContext();
        //Single():返回序列唯一的元素，如果不是唯一，则引发异常
        var result = db.Myinfo.Single(res => res.Id == id);
        result.Content = content;
        result.Author = author;
        result.Click_Number = clickNumber;
        db.SubmitChanges();
    }

    private void insertData(Myinfo data)
    {
        var db = new TestDBDataContext();
        db.Myinfo.InsertOnSubmit(data);
        db.SubmitChanges();
    }

    private void deleteData(int deleteId)
    {
        var db = new TestDBDataContext();
        var data = db.Myinfo.Single(res => res.Id == deleteId);
        db.Myinfo.DeleteOnSubmit(data);
        db.SubmitChanges();
    }

}